/**
 * WordSetUtil.java [V 1.0.0]
 * classes : org.xiaocq.wordsgod.db.WordSetUtil
 * xiaocq Create at 2012-6-23 下午10:35:31
 */
package com.xiaocq.wordsgod.db;

import java.io.BufferedReader;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import com.xiaocq.wordsgod.io.FileIOHelper;

/**
 * org.xiaocq.wordsgod.db.WordSetUtil
 * 
 * @author Mystory Create at 2012-6-23 下午10:35:31
 */
public class WordSetUtil {

	private static final String[] VOCABULARY_COLUMNS = new String[] {
			DVocabulary._ID, DVocabulary.ENGLISH, DVocabulary.CHINESE};

	private static final String[] VOCABULARY_ID = new String[] { DVocabulary._ID };
	
	private Context context;
	private SQLiteDatabase db;
	private DatabaseHelper mOpenHelper;

	public WordSetUtil(Context context) {
		this.context = context;
	}

	/**
	 * 将数据导入数据库
	 */
	public void importDatabase() {
		FileIOHelper fileHelper = new FileIOHelper(context);
		String sql = "INSERT INTO " + DVocabulary.TABLE_NAME + " ( "
				+ DVocabulary.ENGLISH + "," + DVocabulary.CHINESE + ","
				+ DVocabulary.UNIT + "," + DVocabulary.STATE + ","
				+ DVocabulary.CURRENT + "," + DVocabulary.TOTAL_COUNT + ","
				+ DVocabulary.WRONG_COUNT + ") VALUES(?,?,?,0,0,0,0)";
		String[] fields;
		SQLiteStatement stmt;

		open();

		try {
			BufferedReader reader = fileHelper
					.getBufferedReaderFromAssets("AllWords.txt");
			stmt = db.compileStatement(sql);
			// 开始事务处理
			db.beginTransaction();

			while (reader.ready()) {
				fields = reader.readLine().split("@");

				stmt.bindString(1, fields[0]);
				stmt.bindString(2, fields[1]);
				stmt.bindString(3, fields[2]);

				stmt.execute();
			}
			stmt.close();
			db.setTransactionSuccessful();
			reader.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
		}

		close();
	}

	/**
	 * 重置数据库
	 */
	public void resetDatabase() {
		String sql = "UPDATE " + DVocabulary.TABLE_NAME + " SET "
				+ DVocabulary.STATE + " = 0," + DVocabulary.TOTAL_COUNT
				+ " = 0," + DVocabulary.WRONG_COUNT + " = 0";
		db.execSQL(sql);
	}

	/**
	 * 根据测试的时间戳获取测试结果
	 * 
	 * @param testTimestamp
	 * @return
	 */
	public Cursor getWordList(long testTimestamp) {
		String sql = DVocabulary.CURRENT + " = " + testTimestamp;

		Cursor cursor;
		cursor = db.query(DVocabulary.TABLE_NAME, VOCABULARY_COLUMNS, sql,
				null, null, null, null);

		return cursor;

	}

	/**
	 * 返回单词编号的列表
	 * 
	 * @param testId
	 * @return
	 */
	public Cursor getWordIdList(int testId) {
		Cursor cursor;
		switch (testId) {
		case 51:
		case 52:
		case 53:
		case 54:
		case 55:
			cursor = getWordListByType(testId, VOCABULARY_ID);
			break;
		default:
			cursor = getWordListByUnitId(testId, VOCABULARY_ID);
			break;
		}

		return cursor;
	}

	public Cursor getAllWord() {
		return getWordList(51);
	}

	/**
	 * 获取指定testId的单词数量
	 * 
	 * @param testId
	 * @return
	 */
	public int getNumByTestId(int testId) {
		Cursor cursor;
		String selection = "";
		switch (testId) {
		case 51:
			selection = "1 = 1";
			break; // 全部单词
		case 52:
			selection = DVocabulary.STATE + " = 0"; // 未测试的单词
			break;
		case 53:
			selection = DVocabulary.STATE + " != 0"; // 已测试过的单词
			break;
		case 54:
			selection = DVocabulary.STATE + " = 1"; // 答对的单词
			break;
		case 55:
			selection = DVocabulary.STATE + " = 2"; // 答错的单词
			break;
		default:
			selection = DVocabulary.UNIT + " = " + testId;
			break;
		}

		cursor = db.rawQuery("SELECT count(*) FROM " + DVocabulary.TABLE_NAME
				+ " WHERE " + selection, null);
		cursor.moveToFirst();
		int result = cursor.getInt(0);
		cursor.close();
		return result;
	}

	public int[] getWrongWordByUnit() {
		Cursor cursor;
		int[] result = new int[50];
		String sql = "SELECT " + DVocabulary.UNIT + ",count(*) FROM " + DVocabulary.TABLE_NAME
				+ " WHERE " + DVocabulary.STATE + " = 2 GROUP BY "
				+ DVocabulary.UNIT;
		cursor = db.rawQuery(sql, null);
		if(cursor != null && cursor.moveToFirst()){
			do{
				result[cursor.getInt(0) - 1] = cursor.getInt(1);
			}while(cursor.moveToNext());
		}
		cursor.close();
		return result;
	}

	/**
	 * 根据测试的id和测试数量，还有题型 返回生成完毕的单词列表
	 * 
	 * @param testId
	 * @return
	 */
	public Cursor getWordList(int testId) {
		Cursor cursor;
		switch (testId) {
		case 51:
		case 52:
		case 53:
		case 54:
		case 55:
			cursor = getWordListByType(testId, VOCABULARY_COLUMNS);
			break;
		default:
			cursor = getWordListByUnitId(testId, VOCABULARY_COLUMNS);
			break;
		}

		return cursor;
	}

	private Cursor getWordListByUnitId(int testId, String[] selectColumns) {
		String selection = DVocabulary.UNIT + " = " + testId;

		Cursor cursor;
		cursor = db.query(DVocabulary.TABLE_NAME, selectColumns, selection,
				null, null, null, null);

		return cursor;
	}

	private Cursor getWordListByType(int typeId, String[] selectColumns) {
		Cursor cursor;
		String selection = null;
		switch (typeId) {
		case 51:
			break; // 全部单词
		case 52:
			selection = DVocabulary.STATE + " = 0"; // 未测试的单词
			break;
		case 53:
			selection = DVocabulary.STATE + " != 0"; // 已测试过的单词
			break;
		case 54:
			selection = DVocabulary.STATE + " = 1"; // 答对的单词
			break;
		case 55:
			selection = DVocabulary.STATE + " = 2"; // 答错的单词
		default:
			break;
		}
		cursor = db.query(DVocabulary.TABLE_NAME, selectColumns, selection,
				null, null, null, null);

		return cursor;
	}
	
	public Cursor getWrongWordByTestId(int testId){
		Cursor cursor;
		if(testId == 55){
			cursor = getWordListByType(55, VOCABULARY_COLUMNS);
			return cursor;
		}
		String selection = DVocabulary.STATE + " = 2 AND " + DVocabulary.UNIT + " = " + testId;
		cursor = db.query(DVocabulary.TABLE_NAME, VOCABULARY_COLUMNS, selection, null, null, null, null);
		return cursor;
	}

	public void updateToDatabase(int[] wordScore) {
		String sql = "UPDATE " + DVocabulary.TABLE_NAME + " SET "
				+ DVocabulary.STATE + " = ?" + " WHERE " + DVocabulary._ID
				+ " = ?";
		SQLiteStatement stmt = db.compileStatement(sql);
		int id;
		int result;
		try {
			db.beginTransaction();
			for (int index : wordScore) {
				id = index / 10;
				result = index % 10;
				result = (result == 0) ? 2 : 1;
				stmt.bindString(1, Integer.toString(result));
				stmt.bindString(2, Integer.toString(id));
				stmt.execute();
			}
			stmt.close();
			db.setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
		}
	}

	/**
	 * 开启数据库连接，若数据库连接不为空 则忽视要求
	 */
	public void open() {
		if (mOpenHelper == null && db == null) {
			mOpenHelper = new DatabaseHelper(context);
			db = mOpenHelper.getReadableDatabase();
		}
	}

	/**
	 * 关闭数据库连接，若数据库连接为空，则忽视
	 */
	public void close() {
		if (mOpenHelper != null && db != null) {
			db.close();
			mOpenHelper.close();

			db = null;
			mOpenHelper = null;
		}
	}
}
